package cs4280.ass2.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import cs4280.ass2.entity.Officer;
import cs4280.ass2.entity.User;

public class OfficerDao extends BaseDao {
	private static final Logger logger = Logger.getLogger(OfficerDao.class);
	private static final String _SELECT_BY_ID = "select * from APP_USER where ID = ? and TYPE = '" + User.USER_TYPE_OFFCIER + "'";
	private static final String _SELECT_ALL = "select * from APP_USER where TYPE = '" + User.USER_TYPE_OFFCIER + "' order by ID asc";
	private static final String _SELECT_ALL_ACTIVE = "select * from APP_USER where TYPE = '" + User.USER_TYPE_OFFCIER + "' and APP_USER_STATUS = '"+ User.APP_USER_ACTIVE +"' order by ID asc";
	private static final String _INSERT_NEW_OFFICER = "INSERT INTO [APP_USER] (CREATE_DATE, CREATE_USER, MODIFY_DATE, MODIFY_USER, FIRST_NAME, LAST_NAME, LOGIN, PASSWORD, TYPE,  APP_USER_STATUS) VALUES (?,?,?,?,?,?,?,?,?,?)";
	private static final String _UPDATE_OFFICER = "UPDATE [APP_USER] SET MODIFY_DATE = ?, MODIFY_USER = ?, FIRST_NAME = ?, LAST_NAME = ?, LOGIN = ?,  APP_USER_STATUS = ? WHERE id = ?";
	private static final String _DELETE_OFFICER = "UPDATE APP_USER SET APP_USER_STATUS = '" + User.APP_USER_DELETED + "' WHERE ID = ?";

	private static OfficerDao officerDao = null;

	public static OfficerDao getInstance() {
		if (officerDao == null)
			officerDao = new OfficerDao();
		return officerDao;
	}

	public Officer retrieveOfficer(long id) {
		Officer officer = null;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.prepareStatement(_SELECT_BY_ID,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			statement.setLong(1, id);
			result = statement.executeQuery();
			if (result.next()) {
				officer = new Officer();
				officer.setId(result.getLong("id"));
				officer.setFirstName(result.getString("first_name"));
				officer.setLastName(result.getString("last_name"));
				officer.setLogin(result.getString("login"));
				officer.setPassword(result.getString("password"));
				officer.setAppUserStatus(StringUtils.trim(result.getString("app_user_status")));
				officer.setType(StringUtils.trim(result.getString("type")));
				officer.setCreateDate(toUtilDate(result.getTimestamp("create_date")));
				officer.setCreateUser(result.getLong("create_user"));
				officer.setModifyDate(toUtilDate(result.getTimestamp("modify_date")));
				officer.setModifyUser(result.getLong("modify_user"));
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieveOfficer ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return officer;
	}
	
	public List<Officer> retrieveAllOfficer() {
		List<Officer> officerList = new ArrayList<Officer>();
		Connection connection = null;
		Statement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			result = statement.executeQuery(_SELECT_ALL);

			Officer officer;
			while (result.next()) {
				officer = this.retrieveOfficer(result.getLong("id"));
				officerList.add(officer);
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieveOfficer ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return officerList;
	}
	
	public List<Officer> retrieveAllActiveOfficer() {
		List<Officer> officerList = new ArrayList<Officer>();
		Connection connection = null;
		Statement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			result = statement.executeQuery(_SELECT_ALL_ACTIVE);

			Officer officer;
			while (result.next()) {
				officer = this.retrieveOfficer(result.getLong("id"));
				officerList.add(officer);
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieveOfficer ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return officerList;
	}

	public void registerOfficer(Officer officer) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = getConnection();
			statement = connection.prepareStatement(_INSERT_NEW_OFFICER,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			
			statement.setTimestamp(1, toSQLDate(officer.getCreateDate()));
			statement.setLong(2, officer.getCreateUser());
			statement.setTimestamp(3, toSQLDate(officer.getModifyDate()));
			statement.setLong(4, officer.getModifyUser());
			statement.setString(5, officer.getFirstName());
			statement.setString(6, officer.getLastName());
			statement.setString(7,officer.getLogin());
			statement.setString(8, officer.getPassword());
			statement.setString(9, User.USER_TYPE_OFFCIER);
			statement.setString(10,officer.getAppUserStatus());
			
			int rows = statement.executeUpdate();
			
		} catch (SQLException e) {
			logger.error("Fail to create officer ", e);
		} finally {
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
	}

	public void updateOfficer(Officer officer) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = getConnection();
			statement = connection.prepareStatement(_UPDATE_OFFICER,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			
			statement.setTimestamp(1, toSQLDate(officer.getModifyDate()));
			statement.setLong(2, officer.getModifyUser());
			statement.setString(3, officer.getFirstName());
			statement.setString(4, officer.getLastName());
			statement.setString(5,officer.getLogin());
			statement.setString(6,officer.getAppUserStatus());
			statement.setLong(7, officer.getId());
			
			int rows = statement.executeUpdate();
			
		} catch (SQLException e) {
			logger.error("Fail to update officer ", e);
		} finally {
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
	}
	
	public void deleteOfficer(long id) {
		Connection connection = null;
		PreparedStatement pstat = null;
		try {
			connection = getConnection();
			pstat = connection.prepareStatement(_DELETE_OFFICER,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			
			pstat.setLong(1, id);
			int rows = pstat.executeUpdate();
			
		} catch (SQLException e) {
			logger.error("Fail to delete officer ", e);
		} finally {
			DbUtils.closeQuietly(pstat);
			DbUtils.closeQuietly(connection);
		}
	}
}
